/**
 * 
 */
package fr.utbm.gi.in56.user;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import fr.utbm.gi.in56.ads.AdState;
import fr.utbm.gi.in56.ads.AdType;
import fr.utbm.gi.in56.database.DatabaseManager;
import fr.utbm.gi.in56.error.ErrorType;
import fr.utbm.gi.in56.message.MessageManager;

/**
 * Implementation of a user manager.
 *
 * @author Adrien Hoffer & Tarik Belabbas
 *
 */
public class UserManager extends DatabaseManager
{
	/**
	 * Parameterized constructor.
	 * 
	 * @param _connection the connection to use to create a statement
	 * @throws SQLException if the connection cannot create a statement
	 */
	public UserManager( Connection _connection )
	{
		super( _connection );
	}

	/**
	 * Connects an user and returns the state of the connection
	 * 
	 * @param _user the userBean related to the user to connect
	 * @param _password the password of the user
	 * @return the state of the connection
	 * @throws SQLException
	 */
	public ErrorType connectUser( UserBean _user, String _password ) throws SQLException
	{
		ResultSet rst = null;
		Statement statement = this.getNewStatement();
		ErrorType result = ErrorType.NONE;
		try
		{
			// Select the user in the database
			rst = statement.executeQuery( "SELECT * FROM users WHERE login='" //$NON-NLS-1$
					+ _user.getLogin().replace( "'", "''" ) + "' " ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

			// Test if the login exists
			if ( rst.first() )
			{
				// Test if the password is ok
				if ( rst.getString( "password" ).compareTo( _password ) == 0 ) //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				{
					// Test if the user is an admin
					if ( rst.getBoolean( "is_admin" ) ) //$NON-NLS-1$
					{
						_user.setIsAdmin( true );
					}
					
					_user.setCounty( this.getCounty( _user.getLogin() ) );
					
					// Set the number of unread messages
					MessageManager msgMg = new MessageManager( this.getConnection() );
					
					_user.setUnreadMessages( msgMg.getUnreadMessagesNb( _user.getLogin() ) );
				}
				else
				{
					result = ErrorType.WRONG_PASSWORD;
				}

			}
			else
			{
				result = ErrorType.WRONG_LOGIN;
			}

		}
		catch ( SQLException e )
		{
			e.printStackTrace();
			
			result = ErrorType.DB_ERROR;
		}
		
		if( rst != null )
		{
			rst.close();
		}

		return result;
	}
	
	/**
	 * Registers an user, check the parameters and returns an ErrorType
	 * 
	 * @param _user the login of the user
	 * @param _password the password of the user
	 * @param _county the county of the user
	 * @param _isAdmin <tt>true</tt> if the user is an admin
	 * @return the error message relative to the registration process
	 */
	public ErrorType registerUser( UserBean _user, String _password,
			boolean _isAdmin )
	{
		ResultSet rst = null;

		int admin = ( _isAdmin ) ? 1 : 0;

		String loginRefactored = new String( _user.getLogin() );
		loginRefactored = loginRefactored.replace( "'", "''" ); //$NON-NLS-1$ //$NON-NLS-2$
		
		_password  = _password.replace( "'", "''" ); //$NON-NLS-1$ //$NON-NLS-2$

		try
		{
			// Check if the login exists
			rst = this.getNewStatement().executeQuery(
					"SELECT login FROM users WHERE login='" + loginRefactored + "' " ); //$NON-NLS-1$ //$NON-NLS-2$
			if ( !rst.first() )
			{
				this.getNewStatement()
						.executeUpdate(
								"INSERT INTO `users` (`login`, `password`, `is_admin`,"+ //$NON-NLS-1$
								"`global_rate`, `county`) VALUES ('" + loginRefactored + "', '" + //$NON-NLS-1$ //$NON-NLS-2$
								_password + "', '" + admin + "','-1','" + _user.getCounty() + "')" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				
				rst.close();
				
				return ErrorType.NONE;
			}
			
			rst.close();
			
			return ErrorType.REGISTER_LOGIN_ALREADY_EXISTS;

		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		
		return ErrorType.DB_ERROR;
	}
    
	/**
	 * Returns the county of an user
	 * 
	 * @param _login the login of the user
	 * @return the county of the user
	 */
	public int getCounty( String _login )
	{
		ResultSet rst = null;
		int county = -1;

		try
		{
			rst = this.getNewStatement().executeQuery( "SELECT county FROM users " + //$NON-NLS-1$
					"WHERE login = '" + _login.replace( "'", "''" ) + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
			if ( rst.first() )
			{
				county = rst.getInt( "county" ); //$NON-NLS-1$
				rst.close();
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return county;
	}
	
	/**
	 * Set the county of an user, check his password and update the related bean
	 * 
	 * @param _user the user
	 * @param _password the current password to check
	 * @param _newCounty the new county of the user
	 * @return the type of error generated by this process
	 */
	public ErrorType setCounty( UserBean _user, String _password, int _newCounty )
	{
		ErrorType result = ErrorType.NONE;
		
		result = checkUserLoginAndPassword( _user.getLogin(), _password );
		
		if( result == ErrorType.NONE )
		{
			try
			{
				Statement statement = this.getNewStatement();

				statement.executeUpdate( "UPDATE users SET county = '" + //$NON-NLS-1$
						_newCounty + "' WHERE login = '" + _user.getLogin().replace( "'", "''" ) + "'" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
				
				_user.setCounty( _newCounty );
			}
			catch ( SQLException e )
			{
				result = ErrorType.DB_ERROR;
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	/**
	 * Set the password of an user
	 * 
	 * @param _login the login
	 * @param _password the password
	 * @param _newPassword the new password
	 * @return ErrorType.NONE if success
	 */
	public ErrorType setPassword( String _login, String _password, String _newPassword )
	{
		ErrorType result = ErrorType.NONE;
		
		result = checkUserLoginAndPassword( _login, _password );
		
		if( result == ErrorType.NONE )
		{
			Statement statement = this.getNewStatement();
			
			try
			{
				statement.executeUpdate( "UPDATE users SET password = '" + //$NON-NLS-1$
						_newPassword.replace( "'","''" ) + "' WHERE login = '" + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
						_login.replace( "'", "''" ) + "'" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			}
			catch ( SQLException e )
			{
				result = ErrorType.DB_ERROR;
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * Add a rate for an user
	 * 
	 * @param _userRated the rated user
	 * @param _userRater the rater user
	 * @param _adId the ad id
	 * @param _adType the type of ad
	 * @param _rate the rate to add
	 * @return ErrorType.NONE if success
	 */
	public ErrorType addRating( String _userRated, String _userRater, int _adId,
			AdType _adType, int _rate )
	{
		ErrorType result = ErrorType.NONE;
		
		Statement statement = this.getNewStatement();
		
		try
		{
			ResultSet rst = statement.executeQuery( "SELECT * FROM ads_" + //$NON-NLS-1$
					_adType.getType() + " WHERE id_ads_" +_adType.getType() + //$NON-NLS-1$
					" = '" + _adId + "'"); //$NON-NLS-1$ //$NON-NLS-2$
			if( rst.first() )
			{
				if( _userRater.compareTo( rst.getString( "login_buyer" ) ) == 0 ) //$NON-NLS-1$
				{
					if( rst.getInt( "state" ) == AdState.WAITING_FOR_RATING.ordinal() ) //$NON-NLS-1$
					{
						getNewStatement().executeUpdate( "UPDATE ads_" +  //$NON-NLS-1$
								_adType.getType() + " SET state = '" + //$NON-NLS-1$
								AdState.WAITING_FOR_SELLER_RATING.ordinal() +
								"' WHERE id_ads_" +_adType.getType() + //$NON-NLS-1$
								" = '" + _adId + "'"); //$NON-NLS-1$ //$NON-NLS-2$)
					}
					else if( rst.getInt( "state" ) == AdState.WAITING_FOR_BUYER_RATING.ordinal()) //$NON-NLS-1$
					{
						getNewStatement().executeUpdate( "UPDATE ads_" +  //$NON-NLS-1$
								_adType.getType() + " SET state = '" + //$NON-NLS-1$
								AdState.CLOSED.ordinal() +
								"' WHERE id_ads_" +_adType.getType() + //$NON-NLS-1$
								" = '" + _adId + "'"); //$NON-NLS-1$ //$NON-NLS-2$)
					}
				}
				else if( _userRater.compareTo( rst.getString( "login_seller" ) ) == 0 ) //$NON-NLS-1$
				{
					if( rst.getInt( "state" ) == AdState.WAITING_FOR_RATING.ordinal() ) //$NON-NLS-1$
					{
						getNewStatement().executeUpdate( "UPDATE ads_" +  //$NON-NLS-1$
								_adType.getType() + " SET state = '" + //$NON-NLS-1$
								AdState.WAITING_FOR_BUYER_RATING.ordinal() +
								"' WHERE id_ads_" +_adType.getType() + //$NON-NLS-1$
								" = '" + _adId + "'"); //$NON-NLS-1$ //$NON-NLS-2$)
					}
					else if( rst.getInt( "state" ) == AdState.WAITING_FOR_SELLER_RATING.ordinal()) //$NON-NLS-1$
					{
						getNewStatement().executeUpdate( "UPDATE ads_" +  //$NON-NLS-1$
								_adType.getType() + " SET state = '" + //$NON-NLS-1$
								AdState.CLOSED.ordinal() +
								"' WHERE id_ads_" +_adType.getType() + //$NON-NLS-1$
								" = '" + _adId + "'"); //$NON-NLS-1$ //$NON-NLS-2$)
					}
				}
			}
			
			
			// Add the rate into the rating table
			statement.executeUpdate( "INSERT INTO `rating` (`login_rater`," + //$NON-NLS-1$
					"`login_rated`,`grade`, id_" + _adType.getType() + "`) " + //$NON-NLS-1$ //$NON-NLS-2$
					"VALUES ('" + _userRater.replace( "'", "''" ) +  //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
					"', '" + _userRated.replace( "'","''" ) + "','" +  //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
					_rate + "','" + _adId + "')"); //$NON-NLS-1$ //$NON-NLS-2$
			
			// Update the user global rate
			// Count the number of rate
			rst = statement.executeQuery( "SELECT grade, (SELECT COUNT(*) FROM rating WHERE " + //$NON-NLS-1$
					"login_rated = '" + _userRated.replace( "'","''" ) +"') As count FROM rating" +  //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
					" WHERE login_rated = '" + _userRated.replace( "'","''" ) +"'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
			
			if( rst.first() )
			{
				int nbGrades = rst.getInt( "count" ); //$NON-NLS-1$
				int gradesSum = 0;
				while( rst.next() )
				{
					gradesSum += rst.getInt( "grade" ); //$NON-NLS-1$
				}
				statement.executeUpdate( "UPDATE users SET global_rate = '" + //$NON-NLS-1$
						+ gradesSum / nbGrades + " WHERE login = '" + _userRated.replace( "'","''" ) + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
						"'"); //$NON-NLS-1$
			}
			
			rst.close();
		}
		catch( SQLException e )
		{
			result = ErrorType.DB_ERROR;
			e.printStackTrace();
		}
		
		return result;
	}
	
	/**
	 * Returns the list of persons which have sent a message to an user concerning
	 * an ad
	 * 
	 * @param _login the login of the user
	 * @param _adType the type of ad
	 * @param _idAd the ad id
	 * @return a List of logins
	 */
	public List< String > getUsersWhichSentAMessageAbout( String _login,
			AdType _adType, int _idAd)
	{
		Statement statement = getNewStatement();
		
		List< String > loginList = new LinkedList< String >();
		
		try
		{
			ResultSet rst = statement.executeQuery( "SELECT * FROM messages" +  //$NON-NLS-1$
					" WHERE login_receiver = '" + _login + "' AND id_ad_" + //$NON-NLS-1$ //$NON-NLS-2$
					_adType.getType() + " ='" + _idAd + "'"); //$NON-NLS-1$ //$NON-NLS-2$
			
			if( rst.first() )
			{
				while( rst.next() )
				{
					if( ! loginList.contains( rst.getString( "login_sender" ) ) )
					{
						loginList.add( rst.getString( "login_sender" ) );
					}
				}
			}
			
			rst = statement.executeQuery( "SELECT * FROM messages" +  //$NON-NLS-1$
					" WHERE login_sender = '" + _login + "' AND id_ad_" + //$NON-NLS-1$ //$NON-NLS-2$
					_adType.getType() + " ='" + _idAd + "'"); //$NON-NLS-1$ //$NON-NLS-2$
			
			if( rst.first() )
			{
				while( rst.next() )
				{
					if(! loginList.contains( rst.getString( "login_sender" ) ) )
					{
						loginList.add( rst.getString( "login_sender" ) );
					}
				}
			}
			rst.close();
		}
		catch( SQLException e)
		{
			e.printStackTrace();
		}
		return loginList;
	}
	
	/**
	 * Checks if the user exists
	 * 
	 * @param _userLogin the user login
	 * @return <tt>true</tt> if the user exists in the database
	 */
	public boolean isValidUser( String _userLogin )
	{
		Statement statement = this.getNewStatement();
		boolean isValid = false;
		
		try
		{
			ResultSet rst = statement.executeQuery( "SELECT * FROM USER WHERE login = '" + //$NON-NLS-1$
					_userLogin + "'"); //$NON-NLS-1$
			if( rst.first() )
			{
				isValid = true;
			}
			
			rst.close();
		}
		catch ( SQLException e )
		{
			isValid = false;
		}
		
		return isValid;
	}
	
	/**
	 * Check if a user exists and if the password associated is correct
	 * 
	 * @param _userLogin the user login
	 * @param _password the password
	 * @return <tt>true</tt> if the user exists and if the password is the good one
	 */
	private ErrorType checkUserLoginAndPassword( String _userLogin, String _password)
	{
		ResultSet rst = null;
		ErrorType result = ErrorType.NONE;
		try
		{
			Statement statement = getNewStatement();
			// Select the user in the database
			rst = statement.executeQuery( "SELECT * FROM users WHERE login='" //$NON-NLS-1$
					+ _userLogin.replace( "'", "''" ) + "' " ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

			// Test if the login exists
			if ( rst.first() )
			{
				// Test if the password is ok
				if ( ! ( rst.getString( "password" ).compareTo( _password ) == 0 ) ) //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				{
					result = ErrorType.WRONG_PASSWORD;
				}
				
			}
			else
			{
				result = ErrorType.WRONG_LOGIN;
			}
			rst.close();
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
			result = ErrorType.DB_ERROR;
		}
		return result;
	}
}
